We want to be able to perform some inserts to other tables and apply some grants if the criteria in an update trigger are met. Once met, the criteria will never change. The grants and inserts may change over time and other files may be added. I find myself in a position where the most logical (to me anyway) solution seems to be one in which I can dynamically run an SQL file.
Here's my pseudocode of how I envision it should work.
CREATE OR REPLACE TRIGGER au_some_tbl
AFTER UPDATE ON some_tbl
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
v_file VARCHAR2(1024); -- excessively large, I know
BEGIN
IF (:NEW.enabled = 1 AND :OLD.enabled = 0) THEN
v_file := '/path/to/sql/files/' || :NEW.name || '.sql';
@@v_file; -- somehow execute the sql file
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
/
So let's say I have a "function" named say_foo in some_tbl and enabled is set to 1 for that row. The trigger should execute the SQL contained in /path/to/sql/files/say_foo.sql.
The problem is that there doesn't appear to be a way to do this. It looks like I have to add a case or a series of if statements to get it work; it also means that the trigger will have to be edited any time we need a new "function" added. I hope that either I am wrong and it can be done or that there is a cleaner solution. Does any one have any ideas?
Re:UTIL_FILE
Mr. Muskrat on 2006-09-05T17:37:19
Possibly... It does sound like it should work.